package com.htsk.city.services.wechat.service;


import cn.dev33.satoken.stp.StpUtil;
import cn.hutool.core.lang.Validator;
import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONUtil;
import com.htsk.city.services.core.dao.*;
import com.htsk.city.services.core.domain.*;
import com.htsk.city.services.core.entity.PageInfo;
import com.htsk.city.services.core.entity.R;
import com.htsk.city.services.wechat.params.TechnicianQueryParam;
import com.htsk.city.services.wechat.vo.TechnicianAddVo;
import com.htsk.city.services.wechat.vo.TechnicianVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.time.Instant;
import java.util.*;

/**
 * @Author 李树洪
 * @Email 2324592742@qq.com
 * @Date 2025/9/8 16:13
 */
@Service
@Slf4j
public class TechnicianService {
    @Resource
    private TechnicianDao technicianDao;
    @Resource
    private TechnicianFavoriteDao technicianFavoriteDao;
    @Resource
    private ServiceSubCategoryDao serviceSubCategoryDao;
    @Resource
    private TechnicianServiceRangesDao technicianServiceRangesDao;
    @Resource
    private TechnicianAvailabilityDao technicianAvailabilityDao;
    @Resource
    private TechnicianServiceSubCategoryDao technicianServiceSubCategoryDao;

    @Resource
    private WxUserDao wxUserDao;

    @PersistenceContext
    private EntityManager entityManager;

    public R<PageInfo> getTechnicians(TechnicianQueryParam technicianQueryParam) {

//        StpUtil.logout(StpUtil.getLoginId());
        WxUser wxUser = null;
        if (StpUtil.isLogin()) {
            wxUser = JSONUtil.toBean((String) StpUtil.getLoginId(),WxUser.class);
        }
        String baseQuery = "select t.id, t.name, t.technician_no as technicianNo, " +
                "                t.avatar, t.rating, t.service_count as serviceCount, t.gender, t.mobile, " +
                "                t.latitude, t.longitude, t.status, UNIX_TIMESTAMP(t.created_at)*1000 as createdAt, UNIX_TIMESTAMP(t.updated_at)*1000 as updatedAt, " +
                " GROUP_CONCAT(distinct s.service_sub_category_name)  serviceSubCategoryNames, " +
                " GROUP_CONCAT(distinct r.service_range) technicianRanges, " +
                " GROUP_CONCAT(distinct a.availability_type) availabilityType, " ;

                // 处理登录用户收藏
                if (wxUser != null && wxUser.getId() != null) {
                    baseQuery+= "  EXISTS (\n" +
                            "        SELECT 1 \n" +
                            "        FROM technician_favorites tf \n" +
                            "        WHERE tf.technician_id = t.id AND tf.user_id = :userId \n" +
                            "    ) AS isFavorite, ";
                } else {
                    baseQuery+= " 0 AS isFavorite, ";
                }

                baseQuery+= " UNIX_TIMESTAMP(min(a.start_time))*1000 startTime, " +
                " UNIX_TIMESTAMP(min(a.end_time))*1000 endTime, " +
                " CASE WHEN NOW() <= min(a.start_time) THEN " +
                " UNIX_TIMESTAMP(min(a.start_time))*1000 " +
                " WHEN NOW() > min(a.start_time) and NOW() <= min(a.end_time) THEN " +
                " UNIX_TIMESTAMP(NOW())*1000 " +
                " WHEN NOW() > min(a.end_time) THEN " +
                " null " +
                " END appointmentTime, " +
                " (6371 * acos(cos(radians(:userLat)) * cos(radians(t.latitude)) " +
                "    * cos(radians(t.longitude) - radians(:userLng)) + sin(radians(:userLat)) " +
                "    * sin(radians(t.latitude)))) AS distance " +
                " from technicians t " +
                " LEFT JOIN technician_service_sub_category ts on t.id=ts.technician_id " +
                " LEFT JOIN service_sub_category s on s.id=ts.service_sub_category_id and s.`status`=1 and s.del_flag=0 " +
                " LEFT JOIN technician_service_ranges r on t.id=r.technician_id " +
                " LEFT JOIN technician_availability a on t.id=a.technician_id and is_available = 1 " +
                " LEFT JOIN wx_user u on t.id=u.org_id " +
                " where t.status=1 " ;

        StringBuilder conditions = new StringBuilder();
        Map<String, Object> params = new HashMap<>();
        // 登录用户师傅列表排除自己,且只查同城师傅
        if (wxUser != null && wxUser.getId() != null) {
            conditions.append(" and t.id <> :id and u.city_id = :cityId ");
            params.put("id", wxUser.getOrgId() == null ? "" : wxUser.getOrgId());
            params.put("cityId", wxUser.getCityId());
        }else if(technicianQueryParam.getCityId() != null){
            // 未登录只查同城师傅
            conditions.append(" and u.city_id = :cityId ");
            params.put("cityId", technicianQueryParam.getCityId());
        }

        Double lat = technicianQueryParam.getUserLat();
        Double lng = technicianQueryParam.getUserLng();
        String name = technicianQueryParam.getName();
        String gender = technicianQueryParam.getGender();
        String isFavorite = technicianQueryParam.getIsFavorite();
        String availabilityType = technicianQueryParam.getAvailabilityType();
        Integer serviceCategoryId = technicianQueryParam.getServiceCategoryId();
        Integer serviceSubCategoryId = technicianQueryParam.getServiceSubCategoryId();
        List<String> serviceRange = technicianQueryParam.getServiceRange();
        Integer page = technicianQueryParam.getPage();
        Integer size = technicianQueryParam.getSize();
        String orderby = technicianQueryParam.getOrderby();
        String ascDesc = technicianQueryParam.getAscDesc();
        if (StringUtils.isNotBlank(availabilityType)) {
            conditions.append(" and a.availability_type = :availabilityType ");
            params.put("availabilityType", availabilityType);
        }
        if (serviceCategoryId != null ) {
            conditions.append(" and s.service_category_id = :serviceCategoryId ");
            params.put("serviceCategoryId", serviceCategoryId);
        }
        if (serviceSubCategoryId != null ) {
            conditions.append(" and s.id = :serviceSubCategoryId ");
            params.put("serviceSubCategoryId", serviceSubCategoryId);
        }
        if (serviceRange != null && serviceRange.size() > 0) {
            conditions.append(" and r.service_range in (:serviceRange) ");
            params.put("serviceRange", serviceRange);
        }

        if (StringUtils.isNotBlank(name)) {
            conditions.append(" AND t.name LIKE :name ");
            params.put("name", "%" + name + "%");
        }
        if (StringUtils.isNotBlank(gender)) {
            conditions.append(" AND t.gender = :gender ");
            params.put("gender", gender);
        }

        // 处理前端只查询用户收藏
        if (StringUtils.equals("1", isFavorite) ){
            if (wxUser == null || wxUser.getId() == null) {
                return R.fail("请先登录");
            }
            conditions.append(" and t.id in ( " +
                    " SELECT technician_id from technician_favorites where user_id = :userId " +
                    " ) ");
            // 处理登录用户收藏
            params.put("userId", wxUser.getId());
        }
        //查询总数,如果总数为0，则返回
        String countQuery = "SELECT count(distinct t.id) from technicians t " +
                " LEFT JOIN technician_service_sub_category ts on t.id=ts.technician_id " +
                " LEFT JOIN service_sub_category s on s.id=ts.service_sub_category_id and s.status=1 and s.del_flag=0 " +
                " LEFT JOIN technician_service_ranges r on t.id=r.technician_id " +
                " LEFT JOIN technician_availability a on t.id=a.technician_id and is_available = 1 " +
                " LEFT JOIN wx_user u on t.id=u.org_id " +
                " where t.status=1 " + conditions.toString();
        Query queryCount = entityManager.createNativeQuery(countQuery);
        // 设置参数
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            queryCount.setParameter(entry.getKey(), entry.getValue());
        }
        long total = ((Number) queryCount.getSingleResult()).longValue();
        if (total == 0) {
            PageInfo pageInfo = new PageInfo<>();
            pageInfo.setPage(page);
            pageInfo.setSize(size);
            pageInfo.setTotal(total);
            pageInfo.setData(new ArrayList<>());
            pageInfo.setTotalPage(0);
            return R.ok(pageInfo);
        }

        // 如果数据不为空,继续查询,添加排序条件
        conditions.append(" GROUP BY t.id ");
        if (orderby != null && !orderby.equals("")) {
            conditions.append(" ORDER BY " + orderby + " " + ascDesc);
        }

        params.put("userLat", lat);
        params.put("userLng", lng);
        // 处理登录用户收藏
        if (wxUser != null && wxUser.getId() != null) {
            params.put("userId", wxUser.getId());
        }
        String fullQuery = baseQuery + conditions.toString();
        if (page != null && page >0 && size != null && size > 0) {
            fullQuery += " LIMIT " + Math.max(0, (page - 1) * size) + "," + size;
        }
        Query query = entityManager.createNativeQuery(fullQuery);
        // 设置参数
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        //设置查询结果映射Map
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        // 获取结果
        List<Map<String,Object>> results = query.getResultList();
        // 将结果转换为TechnicianVO,师傅类型 和 服务范围 自动转化为list
//        List<TechnicianVO> technicianVOS = JSONUtil.toList(JSONUtil.toJsonStr(results), TechnicianVO.class);
        //增加查师傅类型 和 服务范围 转化
        results.forEach(r -> {
            String techniciansTypes = (String) r.get("serviceSubCategoryNames");
            String technicianRanges = (String) r.get("technicianRanges");
            if (StringUtils.isNotBlank(techniciansTypes)){
                //根据, 分割
                r.put("serviceSubCategoryNames", Arrays.asList(techniciansTypes.split(",")));
            }
            if (StringUtils.isNotBlank(technicianRanges)){
                r.put("technicianRanges", Arrays.asList(technicianRanges.split(",")));
            }
        });
        PageInfo pageInfo = new PageInfo<>();
        pageInfo.setPage(page);
        pageInfo.setSize(size);
        pageInfo.setTotal(total);
        pageInfo.setData(results);
        pageInfo.setTotalPage(size==null?1:(int) Math.ceil((double) total / size));
        return R.ok(pageInfo);
    }


    public R<List<Map<String,Object>>> queryTechnicianType(Long cityId) {
        return R.ok(serviceSubCategoryDao.queryByCityId(cityId));
    }

    public R<Integer> addTechnicianFavorite(WxUser wxUser, Integer technicianId) {
        Long userId = wxUser.getId();
        Long technicianIdL = Long.valueOf(technicianId);
        // 检查技师是否存在
        if (!technicianDao.existsById(technicianIdL)) {
            return R.fail("师傅不存在");
        }
        // 检查是否已收藏
        if (technicianFavoriteDao.existsByUserIdAndTechnicianId(userId, technicianIdL)) {
            return R.fail("用户已收藏该师傅");
        }
        technicianFavoriteDao.save(new TechnicianFavorite(userId, technicianIdL));
        return R.ok();
    }

    public R<Integer> delTechnicianFavorite(WxUser wxUser, Integer technicianId) {
        Long userId = wxUser.getId();
        Long technicianIdL = Long.valueOf(technicianId);
        if (!technicianFavoriteDao.existsByUserIdAndTechnicianId(userId, technicianIdL)) {
            return R.fail("用户未收藏该师傅");
        }
        technicianFavoriteDao.deleteByUserIdAndTechnicianId(userId, technicianIdL);
        return R.ok();
    }

    public R<TechnicianVO> technicianDetail(Long technicianId) {
        Technician technician = technicianDao.findById(technicianId).orElse(null);
        if (technician == null) {
            return R.fail("师傅不存在");
        }
        // 查询师傅服务范围
        List<TechnicianServiceRanges> technicianServiceRanges = technicianServiceRangesDao.findByTechnicianId(technicianId);
        //查询师服务项目
        List<Map<String, Object>> technicianServiceSubCategory = technicianDao.findServiceSubCategoryByTechnicianId(technicianId);
        // 查询师傅服务时间
        TechnicianAvailability technicianAvailability = technicianAvailabilityDao.findOneByTechnicianIdAndIsAvailable(technicianId, 1);
        TechnicianVO technicianVO = new TechnicianVO(technician, technicianServiceRanges, technicianServiceSubCategory, technicianAvailability);
        return R.ok(technicianVO);
    }
    @Transactional
    public R addTechnician(WxUser wxUser, TechnicianAddVo technicianAddVo) {
        // 查询数据库用户,防止session未更新重复添加
        WxUser wxUserOpt = wxUserDao.findById(wxUser.getId()).orElse(null);
        if(wxUserOpt.getUserType() == 2 && wxUserOpt.getOrgId() != null){
            return R.fail("您已经成为专家,请不要重复操作");
        }
        // 1. 验证参数
        if (technicianAddVo == null) {
            return R.fail("参数错误");
        }
        if (StringUtils.isBlank(technicianAddVo.getName())) {
            return R.fail("姓名不能为空");
        }
        if (StringUtils.isBlank(technicianAddVo.getMobile())) {
            return R.fail("手机号不能为空");
        }
        if (!Validator.isMobile(technicianAddVo.getMobile())) {
            return R.fail("手机号格式错误");
        }
        if (technicianAddVo.getLatitude()==null || technicianAddVo.getLongitude()==null) {
            return R.fail("经纬度不能为空");
        }
        if (technicianAddVo.getServiceRange()==null || technicianAddVo.getServiceRange().size()==0) {
            return R.fail("服务范围不能为空");
        }
        if (technicianAddVo.getServiceSubCategoryId()==null || technicianAddVo.getServiceSubCategoryId().size()==0) {
            return R.fail("服务类型不能为空");
        }
        if (technicianAddVo.getStartTime()==null || technicianAddVo.getEndTime()==null) {
            return R.fail("请传入服务时间范围");
        }
        //  2.保存师傅数据
        Technician technician = new Technician();
        technician.setName(technicianAddVo.getName());
        technician.setTechnicianNo(IdUtil.simpleUUID());
        technician.setAvatar(technicianAddVo.getAvatar());
        technician.setGender(technicianAddVo.getGender());
        technician.setMobile(technicianAddVo.getMobile());
        technician.setLatitude(technicianAddVo.getLatitude());
        technician.setLongitude(technicianAddVo.getLongitude());
        technician.setStatus("1");
        technician.setCreatedAt(new Date());
        technician.setUpdatedAt(new Date());
        Technician technicianOpt = technicianDao.save(technician);
        Long technicianId = technicianOpt.getId();
        // 3. 保存服务范围
        for (String serviceRange : technicianAddVo.getServiceRange()) {
            TechnicianServiceRanges technicianServiceRanges = new TechnicianServiceRanges();
            technicianServiceRanges.setTechnicianId(technicianId);
            technicianServiceRanges.setServiceRange(serviceRange);
            technicianServiceRangesDao.save(technicianServiceRanges);
        }
        // 4. 保存服务项目
        for (Long serviceSubCategoryId : technicianAddVo.getServiceSubCategoryId()) {
            TechnicianServiceSubCategory technicianServiceSubCategory = new TechnicianServiceSubCategory();
            technicianServiceSubCategory.setServiceSubCategoryId(serviceSubCategoryId);
            technicianServiceSubCategory.setTechnicianId(technicianId);
            technicianServiceSubCategoryDao.save(technicianServiceSubCategory);
        }
        // 5. 保存服务时间段
        TechnicianAvailability technicianAvailability = new TechnicianAvailability();
        technicianAvailability.setTechnicianId(technicianId);
        technicianAvailability.setStartTime(technicianAddVo.getStartTime());
        technicianAvailability.setEndTime(technicianAddVo.getEndTime());
        technicianAvailability.setIsAvailable(1);
        technicianAvailabilityDao.save(technicianAvailability);
        // 6. 绑定用户
        wxUserOpt.setUserType(2);
        wxUserOpt.setOrgId(technicianId);
        wxUserDao.save(wxUserOpt);
        return R.ok();
    }

}
